﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.IO;
using System.Data.OleDb;
using System.Data;
using IQReportes.Models;

namespace IQReportes
{
    public class Excel
    {
        public List<Transaccion> obtenerTransaccionesCsv(string path, string nombreArch)
        {
            path = path + "\\" + nombreArch;
            string[] csvData = File.ReadAllLines(path);
            var transacciones = (from i in csvData
                                let rowData = i.Split(',') //splits each row into an array called rowData
                                select new Transaccion
                                {
                                    vcHora = rowData[0],
                                    vcNemonico = rowData[1],
                                    vcDIR = rowData[2],
                                    vcEstado = rowData[3],
                                    vcNombreTransaccion = rowData[4],
                                    dbMonto = Double.Parse(rowData[11]),
                                    inMoneda = Int32.Parse(rowData[12])
                                }).ToList();
            return transacciones;
        }

        public DataSet obtenerDataExcel(string path, string nombreArch, string nombreHoja)
        {
            //Definir el path la conexion
            path = path + "\\" + nombreArch;
            String sConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;";
            OleDbConnection objConn = new OleDbConnection(sConnectionString);
            try
            {
                objConn.Open();
                OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [" + nombreHoja + "$]", objConn);
                OleDbDataAdapter objAdapter = new OleDbDataAdapter();
                objAdapter.SelectCommand = objCmdSelect;
                 
                //llenar el data set con la data obtenida del excel
                DataSet objDataset = new DataSet();
                objAdapter.Fill(objDataset);
                objConn.Close();

                return objDataset;
            }
            catch (Exception e)
            {
                return null;
            }
        }

    }
}